{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 利用LightGBM/XGboost实现Happy Customer Bank目标客户（贷款成功的客户）识别\n",
    "    \n",
    "** 任务说明: ** \n",
    "    \n",
    "Happy Customer Bank目标客户识别Happy Customer Bank目标客户识别\n",
    "\n",
    "https://discuss.analyticsvidhya.com/t/hackathon-3-x-predict-customer-worth-for-happy-customer-bank/3802\n",
    "\n",
    "** 文件说明: **\n",
    "\n",
    "Train.csv：训练数据\n",
    "\n",
    "Test.csv：测试数据\n",
    "\n",
    "** 字段说明: **\n",
    "\n",
    "数据集共26个字段: 其中1-24列为输入特征，25-26列为输出特征。\n",
    "    \n",
    "输入特征：\n",
    "    \n",
    "1.\tID - 唯一ID（不能用于预测）\n",
    "2.\tGender - 性别\n",
    "3.\tCity - 城市\n",
    "4.\tMonthly_Income - 月收入（以卢比为单位）\n",
    "5.\tDOB - 出生日期\n",
    "6.\tLead_Creation_Date - 潜在（贷款）创建日期\n",
    "7.\tLoan_Amount_Applied - 贷款申请请求金额（印度卢比，INR）\n",
    "8.\tLoan_Tenure_Applied - 贷款申请期限（单位为年）\n",
    "9.\tExisting_EMI -现有贷款的EMI（EMI：电子货币机构许可证） \n",
    "10.\tEmployer_Name雇主名称\n",
    "11.\tSalary_Account - 薪资帐户银行\n",
    "12.\tMobile_Verified - 是否移动验证（Y / N）\n",
    "13.\tVAR5 - 连续型变量\n",
    "14.\tVAR1-  类别型变量\n",
    "15.\tLoan_Amount_Submitted - 提交的贷款金额（在看到资格后修改和选择）\n",
    "16.\tLoan_Tenure_Submitted - 提交的贷款期限（单位为年，在看到资格后修改和选择）\n",
    "17.\tInterest_Rate - 提交贷款金额的利率\n",
    "18.\tProcessing_Fee - 提交贷款的处理费（INR）\n",
    "19.\tEMI_Loan_Submitted -提交的EMI贷款金额（INR）\n",
    "20.\tFilled_Form - 后期报价后是否已填写申请表格\n",
    "21.\tDevice_Type - 进行申请的设备（浏览器/移动设备）\n",
    "22.\tVar2 - 类别型变量\n",
    "23.\tSource - 类别型变量\n",
    "24.\tVar4 - 类别型变量\n",
    "\n",
    "输出：\n",
    "\n",
    "25.\tLoggedIn - 是否login（只用于理解问题的变量，不能用于预测，测试集中没有）\n",
    "26. Disbursed - 是否发放贷款（目标变量），1为发放贷款（目标客户）\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** 作业要求：**\n",
    "\n",
    "1.\t适当的特征工程（20分）\n",
    "2.\t用LightGBM完成任务，并用交叉验证对模型的超参数（learning_rate、n_estimators、num_leaves、max_depth、min_data_in_leaf、colsample_bytree、subsample）进行调优。（70分）\n",
    "或者用XGBoost完成任务，并用交叉验证对模型的超参数（learning_rate、n_estimators、max_depth、min_child_weight、colsample_bytree、subsample、reg_lambda、reg_）进行调优。\n",
    "3.\t对最终模型给出特征重要性（10分）\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 导入工具包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Load data:\n",
    "dpath = './Datas/'\n",
    "train = pd.read_csv(dpath +'Train.csv', encoding = 'latin1')\n",
    "test = pd.read_csv(dpath +'Test.csv', encoding = 'latin1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 87020 entries, 0 to 87019\n",
      "Data columns (total 26 columns):\n",
      "ID                       87020 non-null object\n",
      "Gender                   87020 non-null object\n",
      "City                     86017 non-null object\n",
      "Monthly_Income           87020 non-null int64\n",
      "DOB                      87020 non-null object\n",
      "Lead_Creation_Date       87020 non-null object\n",
      "Loan_Amount_Applied      86949 non-null float64\n",
      "Loan_Tenure_Applied      86949 non-null float64\n",
      "Existing_EMI             86949 non-null float64\n",
      "Employer_Name            86949 non-null object\n",
      "Salary_Account           75256 non-null object\n",
      "Mobile_Verified          87020 non-null object\n",
      "Var5                     87020 non-null int64\n",
      "Var1                     87020 non-null object\n",
      "Loan_Amount_Submitted    52407 non-null float64\n",
      "Loan_Tenure_Submitted    52407 non-null float64\n",
      "Interest_Rate            27726 non-null float64\n",
      "Processing_Fee           27420 non-null float64\n",
      "EMI_Loan_Submitted       27726 non-null float64\n",
      "Filled_Form              87020 non-null object\n",
      "Device_Type              87020 non-null object\n",
      "Var2                     87020 non-null object\n",
      "Source                   87020 non-null object\n",
      "Var4                     87020 non-null int64\n",
      "LoggedIn                 87020 non-null int64\n",
      "Disbursed                87020 non-null int64\n",
      "dtypes: float64(8), int64(5), object(13)\n",
      "memory usage: 17.3+ MB\n"
     ]
    }
   ],
   "source": [
    "train.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 37717 entries, 0 to 37716\n",
      "Data columns (total 24 columns):\n",
      "ID                       37717 non-null object\n",
      "Gender                   37717 non-null object\n",
      "City                     37319 non-null object\n",
      "Monthly_Income           37717 non-null int64\n",
      "DOB                      37717 non-null object\n",
      "Lead_Creation_Date       37717 non-null object\n",
      "Loan_Amount_Applied      37677 non-null float64\n",
      "Loan_Tenure_Applied      37677 non-null float64\n",
      "Existing_EMI             37677 non-null float64\n",
      "Employer_Name            37675 non-null object\n",
      "Salary_Account           32680 non-null object\n",
      "Mobile_Verified          37717 non-null object\n",
      "Var5                     37717 non-null int64\n",
      "Var1                     37717 non-null object\n",
      "Loan_Amount_Submitted    22795 non-null float64\n",
      "Loan_Tenure_Submitted    22795 non-null float64\n",
      "Interest_Rate            12110 non-null float64\n",
      "Processing_Fee           11971 non-null float64\n",
      "EMI_Loan_Submitted       12110 non-null float64\n",
      "Filled_Form              37717 non-null object\n",
      "Device_Type              37717 non-null object\n",
      "Var2                     37717 non-null object\n",
      "Source                   37717 non-null object\n",
      "Var4                     37717 non-null int64\n",
      "dtypes: float64(8), int64(3), object(13)\n",
      "memory usage: 6.9+ MB\n"
     ]
    }
   ],
   "source": [
    "test.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(124737, 27)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Combine into data:\n",
    "train['DataType']= 'train'\n",
    "test['DataType'] = 'test'\n",
    "data=pd.concat([train, test],ignore_index=True, sort=False)\n",
    "data.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check missings:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID                           0\n",
       "Gender                       0\n",
       "City                      1401\n",
       "Monthly_Income               0\n",
       "DOB                          0\n",
       "Lead_Creation_Date           0\n",
       "Loan_Amount_Applied        111\n",
       "Loan_Tenure_Applied        111\n",
       "Existing_EMI               111\n",
       "Employer_Name              113\n",
       "Salary_Account           16801\n",
       "Mobile_Verified              0\n",
       "Var5                         0\n",
       "Var1                         0\n",
       "Loan_Amount_Submitted    49535\n",
       "Loan_Tenure_Submitted    49535\n",
       "Interest_Rate            84901\n",
       "Processing_Fee           85346\n",
       "EMI_Loan_Submitted       84901\n",
       "Filled_Form                  0\n",
       "Device_Type                  0\n",
       "Var2                         0\n",
       "Source                       0\n",
       "Var4                         0\n",
       "LoggedIn                 37717\n",
       "Disbursed                37717\n",
       "DataType                     0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.apply(lambda x: sum(x.isnull()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dealing with features which have missing datas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** 1. Drop the following features since each feature includes too many categories: **\n",
    "    \n",
    "    * City (with missing=1401)\n",
    "    \n",
    "    * Employer_Name (with missing=113)\n",
    "    \n",
    "    * Salary_Account (with missing=16801)\n",
    "    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** Drop \"City\" **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 724 different cities in the \"City\" feature.\n"
     ]
    }
   ],
   "source": [
    "City_count = len(data['City'].unique())\n",
    "print('There are %i' % City_count,  'different cities in the \"City\" feature.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above result suggests there are too many categories in the \"City\" feature, so I will choose to drop this feature.\n",
    "\n",
    "There are some other ways to deal with such kind of feature, for example, I can count only the important cities and put all other inimportant cities to \"OtherCities\". I might try this way in the future."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.drop('City',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** Drop \"Employer_Name\" **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 57193 different employers in the \"Employer_Name\" feature.\n"
     ]
    }
   ],
   "source": [
    "Employer_count = len(data['Employer_Name'].value_counts())\n",
    "print('There are %i' % Employer_count,  'different employers in the \"Employer_Name\" feature.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the same reason of dropping \"City\", I will do the same on \"Employer_Name\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.drop('Employer_Name',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** Drop \"Salary_Account\" **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 58 different salary accounts in the \"Salary_Account\" feature.\n"
     ]
    }
   ],
   "source": [
    "Salary_Account = len(data['Salary_Account'].value_counts())\n",
    "print('There are %i' % Salary_Account,  'different salary accounts in the \"Salary_Account\" feature.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.drop('Salary_Account',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** 2. The following features have majority missings, so I'll introduce a new variable to tell whether this feature is missing and then delete this feature. **\n",
    "\n",
    "   * Loan_Amount_Submitted  (missing=49535)\n",
    "    \n",
    "   * Loan_Tenure_Submitted  (missing=49535)\n",
    "\n",
    "   * Interest_Rate          (missing=84901)\n",
    "    \n",
    "   * Processing_Fee         (missing=85346)\n",
    "\n",
    "   * EMI_Loan_Submitted     (missing=84901)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create new features indicating whether missing or not\n",
    "data['Loan_Amount_Submitted_Missing'] = data['Loan_Amount_Submitted'].apply(lambda x: 1 if pd.isnull(x) else 0)\n",
    "data['Loan_Tenure_Submitted_Missing'] = data['Loan_Tenure_Submitted'].apply(lambda x: 1 if pd.isnull(x) else 0)\n",
    "data['Interest_Rate_Missing'] = data['Interest_Rate'].apply(lambda x: 1 if pd.isnull(x) else 0)\n",
    "data['Processing_Fee_Missing'] = data['Processing_Fee'].apply(lambda x: 1 if pd.isnull(x) else 0)\n",
    "data['EMI_Loan_Submitted_Missing'] = data['EMI_Loan_Submitted'].apply(lambda x: 1 if pd.isnull(x) else 0)\n",
    "\n",
    "# Delete original features\n",
    "data.drop(['Loan_Amount_Submitted','Loan_Tenure_Submitted','Interest_Rate','Processing_Fee','EMI_Loan_Submitted'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** 3. Filling the missing in the following features with their's median: **\n",
    "\n",
    "   * Loan_Amount_Applied (missing=111)\n",
    "    \n",
    "   * Loan_Tenure_Applied (missing=111)\n",
    "\n",
    "   * Existing_EMI        (missing=111)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['Loan_Amount_Applied'].fillna(data['Loan_Amount_Applied'].median(),inplace=True)\n",
    "data['Loan_Tenure_Applied'].fillna(data['Loan_Tenure_Applied'].median(),inplace=True)\n",
    "data['Existing_EMI'].fillna(data['Existing_EMI'].median(),inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Drop features with insignificant influence to the result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Drop the following features since they intuitively have no big influence on the results:\n",
    "    \n",
    "    * Lead_Creation_Date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.drop('Lead_Creation_Date',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 124737 entries, 0 to 124736\n",
      "Data columns (total 23 columns):\n",
      "ID                               124737 non-null object\n",
      "Gender                           124737 non-null object\n",
      "Monthly_Income                   124737 non-null int64\n",
      "DOB                              124737 non-null object\n",
      "Loan_Amount_Applied              124737 non-null float64\n",
      "Loan_Tenure_Applied              124737 non-null float64\n",
      "Existing_EMI                     124737 non-null float64\n",
      "Mobile_Verified                  124737 non-null object\n",
      "Var5                             124737 non-null int64\n",
      "Var1                             124737 non-null object\n",
      "Filled_Form                      124737 non-null object\n",
      "Device_Type                      124737 non-null object\n",
      "Var2                             124737 non-null object\n",
      "Source                           124737 non-null object\n",
      "Var4                             124737 non-null int64\n",
      "LoggedIn                         87020 non-null float64\n",
      "Disbursed                        87020 non-null float64\n",
      "DataType                         124737 non-null object\n",
      "Loan_Amount_Submitted_Missing    124737 non-null int64\n",
      "Loan_Tenure_Submitted_Missing    124737 non-null int64\n",
      "Interest_Rate_Missing            124737 non-null int64\n",
      "Processing_Fee_Missing           124737 non-null int64\n",
      "EMI_Loan_Submitted_Missing       124737 non-null int64\n",
      "dtypes: float64(5), int64(8), object(10)\n",
      "memory usage: 21.9+ MB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dealing the left-over objective feathers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Frequency count for variable Gender\n",
      "Male      71398\n",
      "Female    53339\n",
      "Name: Gender, dtype: int64\n",
      "\n",
      "Frequency count for variable Mobile_Verified\n",
      "Y    80928\n",
      "N    43809\n",
      "Name: Mobile_Verified, dtype: int64\n",
      "\n",
      "Frequency count for variable Var1\n",
      "HBXX    84901\n",
      "HBXC    12952\n",
      "HBXB     6502\n",
      "HAXA     4214\n",
      "HBXA     3042\n",
      "HAXB     2879\n",
      "HBXD     2818\n",
      "HAXC     2171\n",
      "HBXH     1387\n",
      "HCXF      990\n",
      "HAYT      710\n",
      "HAVC      570\n",
      "HAXM      386\n",
      "HCXD      348\n",
      "HCYS      318\n",
      "HVYS      252\n",
      "HAZD      161\n",
      "HCXG      114\n",
      "HAXF       22\n",
      "Name: Var1, dtype: int64\n",
      "\n",
      "Frequency count for variable Filled_Form\n",
      "N    96740\n",
      "Y    27997\n",
      "Name: Filled_Form, dtype: int64\n",
      "\n",
      "Frequency count for variable Device_Type\n",
      "Web-browser    92105\n",
      "Mobile         32632\n",
      "Name: Device_Type, dtype: int64\n",
      "\n",
      "Frequency count for variable Var2\n",
      "B    53481\n",
      "G    47338\n",
      "C    20366\n",
      "E     1855\n",
      "D      918\n",
      "F      770\n",
      "A        9\n",
      "Name: Var2, dtype: int64\n",
      "\n",
      "Frequency count for variable Source\n",
      "S122    55249\n",
      "S133    42900\n",
      "S159     7999\n",
      "S143     6140\n",
      "S127     2804\n",
      "S137     2450\n",
      "S134     1900\n",
      "S161     1109\n",
      "S151     1018\n",
      "S157      929\n",
      "S153      705\n",
      "S144      447\n",
      "S156      432\n",
      "S158      294\n",
      "S123      112\n",
      "S141       83\n",
      "S162       60\n",
      "S124       43\n",
      "S150       19\n",
      "S160       11\n",
      "S138        5\n",
      "S136        5\n",
      "S155        5\n",
      "S129        4\n",
      "S139        4\n",
      "S135        2\n",
      "S132        1\n",
      "S154        1\n",
      "S140        1\n",
      "S131        1\n",
      "S126        1\n",
      "S125        1\n",
      "S130        1\n",
      "S142        1\n",
      "Name: Source, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Count how many elements in each category of all object variables:\n",
    "\n",
    "var = ['Gender','Mobile_Verified','Var1','Filled_Form','Device_Type','Var2','Source']\n",
    "for v in var:\n",
    "    print('\\nFrequency count for variable %s'%v)\n",
    "    print(data[v].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**1. Regroup the feature \"Source\" **\n",
    "\n",
    "The above suggests the feature \"Source\" has two dominant catagories ** S122, S133 ** and \n",
    "many unimportant catagories. So I will manually regroup them as:\n",
    "    \n",
    "    * S122\n",
    "    \n",
    "    * S122\n",
    "    \n",
    "    * Others"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "S122      55249\n",
       "S133      42900\n",
       "others    26588\n",
       "Name: Source, dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['Source'] = data['Source'].apply(lambda x: 'others' if x not in ['S122','S133'] else x)\n",
    "data['Source'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** 2. Applying One-Hot Encoding to the following *object* variables: **\n",
    "    \n",
    "    * Gender  \n",
    "    \n",
    "    * Mobile_Verified \n",
    "    \n",
    "    * Var1 \n",
    "    \n",
    "    * Filled_Form \n",
    "    \n",
    "    * Device_Type \n",
    "    \n",
    "    * Var2\n",
    "    \n",
    "    * Source"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** One-Hot Encoding to object variables **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import LabelEncoder\n",
    "le = LabelEncoder()\n",
    "var_to_encode = ['Device_Type','Filled_Form','Gender','Var1','Var2','Mobile_Verified','Source']\n",
    "for col in var_to_encode:\n",
    "    data[col] = le.fit_transform(data[col])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ID', 'Monthly_Income', 'DOB', 'Loan_Amount_Applied',\n",
       "       'Loan_Tenure_Applied', 'Existing_EMI', 'Var5', 'Var4', 'LoggedIn',\n",
       "       'Disbursed', 'DataType', 'Loan_Amount_Submitted_Missing',\n",
       "       'Loan_Tenure_Submitted_Missing', 'Interest_Rate_Missing',\n",
       "       'Processing_Fee_Missing', 'EMI_Loan_Submitted_Missing', 'Device_Type_0',\n",
       "       'Device_Type_1', 'Filled_Form_0', 'Filled_Form_1', 'Gender_0',\n",
       "       'Gender_1', 'Var1_0', 'Var1_1', 'Var1_2', 'Var1_3', 'Var1_4', 'Var1_5',\n",
       "       'Var1_6', 'Var1_7', 'Var1_8', 'Var1_9', 'Var1_10', 'Var1_11', 'Var1_12',\n",
       "       'Var1_13', 'Var1_14', 'Var1_15', 'Var1_16', 'Var1_17', 'Var1_18',\n",
       "       'Var2_0', 'Var2_1', 'Var2_2', 'Var2_3', 'Var2_4', 'Var2_5', 'Var2_6',\n",
       "       'Mobile_Verified_0', 'Mobile_Verified_1', 'Source_0', 'Source_1',\n",
       "       'Source_2'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.get_dummies(data, columns=var_to_encode)\n",
    "data.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dealing with other features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** Use \"Age\" to replace the feature \"DOB\" **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create \"Age\" feature:\n",
    "data['Age'] = data['DOB'].apply(lambda x: 118 - int(x[-2:])) # means: 2018-1987 = 118 - 87\n",
    "\n",
    "# Drop \"DOB\" feature:\n",
    "data.drop('DOB',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** Other drops **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.drop('LoggedIn',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check the final Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 124737 entries, 0 to 124736\n",
      "Data columns (total 52 columns):\n",
      "ID                               124737 non-null object\n",
      "Monthly_Income                   124737 non-null int64\n",
      "Loan_Amount_Applied              124737 non-null float64\n",
      "Loan_Tenure_Applied              124737 non-null float64\n",
      "Existing_EMI                     124737 non-null float64\n",
      "Var5                             124737 non-null int64\n",
      "Var4                             124737 non-null int64\n",
      "Disbursed                        87020 non-null float64\n",
      "DataType                         124737 non-null object\n",
      "Loan_Amount_Submitted_Missing    124737 non-null int64\n",
      "Loan_Tenure_Submitted_Missing    124737 non-null int64\n",
      "Interest_Rate_Missing            124737 non-null int64\n",
      "Processing_Fee_Missing           124737 non-null int64\n",
      "EMI_Loan_Submitted_Missing       124737 non-null int64\n",
      "Device_Type_0                    124737 non-null uint8\n",
      "Device_Type_1                    124737 non-null uint8\n",
      "Filled_Form_0                    124737 non-null uint8\n",
      "Filled_Form_1                    124737 non-null uint8\n",
      "Gender_0                         124737 non-null uint8\n",
      "Gender_1                         124737 non-null uint8\n",
      "Var1_0                           124737 non-null uint8\n",
      "Var1_1                           124737 non-null uint8\n",
      "Var1_2                           124737 non-null uint8\n",
      "Var1_3                           124737 non-null uint8\n",
      "Var1_4                           124737 non-null uint8\n",
      "Var1_5                           124737 non-null uint8\n",
      "Var1_6                           124737 non-null uint8\n",
      "Var1_7                           124737 non-null uint8\n",
      "Var1_8                           124737 non-null uint8\n",
      "Var1_9                           124737 non-null uint8\n",
      "Var1_10                          124737 non-null uint8\n",
      "Var1_11                          124737 non-null uint8\n",
      "Var1_12                          124737 non-null uint8\n",
      "Var1_13                          124737 non-null uint8\n",
      "Var1_14                          124737 non-null uint8\n",
      "Var1_15                          124737 non-null uint8\n",
      "Var1_16                          124737 non-null uint8\n",
      "Var1_17                          124737 non-null uint8\n",
      "Var1_18                          124737 non-null uint8\n",
      "Var2_0                           124737 non-null uint8\n",
      "Var2_1                           124737 non-null uint8\n",
      "Var2_2                           124737 non-null uint8\n",
      "Var2_3                           124737 non-null uint8\n",
      "Var2_4                           124737 non-null uint8\n",
      "Var2_5                           124737 non-null uint8\n",
      "Var2_6                           124737 non-null uint8\n",
      "Mobile_Verified_0                124737 non-null uint8\n",
      "Mobile_Verified_1                124737 non-null uint8\n",
      "Source_0                         124737 non-null uint8\n",
      "Source_1                         124737 non-null uint8\n",
      "Source_2                         124737 non-null uint8\n",
      "Age                              124737 non-null int64\n",
      "dtypes: float64(4), int64(9), object(2), uint8(37)\n",
      "memory usage: 18.7+ MB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID                                   0\n",
       "Monthly_Income                       0\n",
       "Loan_Amount_Applied                  0\n",
       "Loan_Tenure_Applied                  0\n",
       "Existing_EMI                         0\n",
       "Var5                                 0\n",
       "Var4                                 0\n",
       "Disbursed                        37717\n",
       "DataType                             0\n",
       "Loan_Amount_Submitted_Missing        0\n",
       "Loan_Tenure_Submitted_Missing        0\n",
       "Interest_Rate_Missing                0\n",
       "Processing_Fee_Missing               0\n",
       "EMI_Loan_Submitted_Missing           0\n",
       "Device_Type_0                        0\n",
       "Device_Type_1                        0\n",
       "Filled_Form_0                        0\n",
       "Filled_Form_1                        0\n",
       "Gender_0                             0\n",
       "Gender_1                             0\n",
       "Var1_0                               0\n",
       "Var1_1                               0\n",
       "Var1_2                               0\n",
       "Var1_3                               0\n",
       "Var1_4                               0\n",
       "Var1_5                               0\n",
       "Var1_6                               0\n",
       "Var1_7                               0\n",
       "Var1_8                               0\n",
       "Var1_9                               0\n",
       "Var1_10                              0\n",
       "Var1_11                              0\n",
       "Var1_12                              0\n",
       "Var1_13                              0\n",
       "Var1_14                              0\n",
       "Var1_15                              0\n",
       "Var1_16                              0\n",
       "Var1_17                              0\n",
       "Var1_18                              0\n",
       "Var2_0                               0\n",
       "Var2_1                               0\n",
       "Var2_2                               0\n",
       "Var2_3                               0\n",
       "Var2_4                               0\n",
       "Var2_5                               0\n",
       "Var2_6                               0\n",
       "Mobile_Verified_0                    0\n",
       "Mobile_Verified_1                    0\n",
       "Source_0                             0\n",
       "Source_1                             0\n",
       "Source_2                             0\n",
       "Age                                  0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.apply(lambda x: sum(x.isnull()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Split train and test data sets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "train = data.loc[data['DataType']=='train']\n",
    "test = data.loc[data['DataType']=='test']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/sddy9/anaconda3/lib/python3.6/site-packages/pandas/core/frame.py:3694: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  errors=errors)\n"
     ]
    }
   ],
   "source": [
    "train.drop('DataType',axis=1,inplace=True)\n",
    "test.drop(['DataType','Disbursed'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.to_csv('train_modified_new.csv',index=False)\n",
    "test.to_csv('test_modified_new.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
